package com.hsnn.medstgmini.pro.plan.dao.impl;

import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.plan.dao.WsjPlanDao;
import com.hsnn.medstgmini.pro.project.dao.BidProjectDao;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;
@Repository("wsjPlanDao")
public class WsjPlanDaoImpl implements WsjPlanDao {
	
	@Autowired
	private BidProjectDao bidProjectDao;
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	private Session getSession(){
		Session session = sessionFactory.getCurrentSession();
		if (null == session){
			session = sessionFactory.openSession();
		}
		return session;
	}
	
	@Autowired
	private HibernateSqlUtil dao;
	
	/**
	 * 
	 *@category 获取区域采购计划 项目列表
	 *@author wangbing
	 *@date 2015年6月12日 下午3:33:22
	 *@param page
	 *@return
	 */
	@Override
	public Pagination getWsjProjectList(Pagination page){
		String name = page.getConditions().get("name");
		String status = page.getConditions().get("status");
		String healbureaId = page.getConditions().get("healbureaId");
		String healbureaCode = getOrgCodeById(healbureaId);
		StringBuffer sql = new StringBuffer()
		.append("	SELECT a.proj_id projId,a.name projName,a.proj_cat projCat,")
		.append("	a.plan_start_datetime startTime,a.plan_end_datetime endTime,a.status status,a.proj_code projCode ")
		.append("	FROM bid_project a LEFT JOIN bid_hospplan b ON b.proj_id = a.proj_id ")
		.append("	WHERE b.hea_bur_code = :healbureaCode ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("healbureaCode", healbureaCode);
		if(StringUtils.isNotBlank(name)){
			sql.append(" and a.name like :name ");
			params.put("name", "%"+name+"%");
		}
		if(StringUtils.isNotBlank(status)){
			sql.append(" and a.status = :status ");
			params.put("status", status);
		}
		sql.append("	GROUP BY a.proj_id ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category  采购计划按地区汇总
	 *@author wangbing
	 *@date 2015年6月18日
	 *List<Object>
	 */
	@Override
	public Pagination getWsjPlanByAreaList(Pagination page){
		String projId = page.getConditions().get("projId");
		String areaId = page.getConditions().get("areaId");
		String healbureaId = page.getConditions().get("healbureaId");
		String healbureaCode = getOrgCodeById(healbureaId);
		StringBuffer sql = new StringBuffer()
		.append("	SELECT a.area_id areaId,b.area_name areaName,b.ar_fullname arFullname, ")
		.append("	b.status status,COUNT(a.drug_code) drugCount ")
		.append("	FROM bid_hospplan a ")
		.append("	LEFT JOIN dic_area b ON b.area_id = a.area_id ") 
		.append("	WHERE a.proj_id = :projId and a.hea_bur_code = :healbureaCode")
		.append("	AND a.area_id LIKE :areaId ")
		.append("	GROUP BY a.area_id order by a.area_id ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("projId", projId);
		params.put("areaId", areaId+"%");
		params.put("healbureaCode", healbureaCode);
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category  采购计划按地区汇总详情（根据id查询）
	 *@author wangbing
	 *@date 2015年6月18日
	 *List<Object>
	 */
	@Override
	public Pagination getWsjPlanDetailsByArea(Pagination page){
		String projId = page.getConditions().get("projId");
		String areaId = page.getConditions().get("areaId");
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String compName = page.getConditions().get("compName");
		String healbureaId = page.getConditions().get("healbureaId");
		String healbureaCode  = getOrgCodeById(healbureaId);
		StringBuffer sql = new StringBuffer()
		.append("	SELECT b.drug_code drugCode, c.qualityspec_name drugName, c.form_name formName, ")
		.append("	c.spec_name specName,b.factor factor, b.unit_name unit, b.material_name materialName,  ")
		.append("	b.comp_name compName,b.comp_code compCode,a.lasty_pro_amt lasyProAmt, a.plan_pro_amt planProAmt, ")
		.append("	a.pro_method proMethod,a.quality_requires qualityRequires, a.proj_id projId,a.drug_code drugId ")
		.append("	FROM bid_hospplan a  ")
		.append("	LEFT JOIN  std_proddruginfo b ON a.drug_code = b.drug_code") 
		.append("	LEFT JOIN   std_drugqualityspec c ON c.qualityspec_id = b.qualityspec_id")
		.append("	WHERE a.proj_id = :projId AND a.area_id = :areaId and a.hea_bur_code = :healbureaCode ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("projId", projId);
		params.put("areaId", areaId);
		params.put("healbureaCode", healbureaCode);
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (c.qualityspec_name like :drugName or c.qualityspec_name_spel like :drugName) ");
			params.put("drugName", "%"+drugName+"%");
		}
		if(StringUtils.isNotBlank(compName)){
			sql.append(" and (b.comp_name like :compName or b.comp_name_spel like :compName )");
			params.put("compName", "%"+compName+"%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 获取当前卫生局可申报采购计划列表
	 *@author wangbing
	 *@date 2015年6月12日 下午2:04:37
	 *@param page
	 *@return
	 */
	@Override
	public Pagination getWsjPlanProjectList(Pagination page){
		String healbureaId = page.getConditions().get("healbureaId");
		String healbureaCode = getOrgCodeById(healbureaId);
		String projCode = page.getConditions().get("projCode");
		String name = page.getConditions().get("name");
		//点击申报采购计划列表（带有已申报的药品）
		StringBuffer sql = new StringBuffer()
		.append("	SELECT   c.proj_id projId,c.status status,")
		.append("	c.proj_code projCode, c.name name ,c.proj_intro projIntro,")
		.append("	c.plan_start_datetime starttime,     ")   
		.append("	c.plan_end_datetime endtime, c.proj_cat projCat,    ")
		.append("	(select count(*) from bid_hospplan a where c.proj_id = a.proj_id and a.hea_bur_code = :healbureaCode) drugCount ")
		.append("	from bid_project c    ")       
		.append("	WHERE      ")
		.append("	c.status = 1  ")
		.append("	AND c.plan_start_datetime < :currentTime ") 
		.append("	AND c.plan_end_datetime > :currentTime ");
		
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("healbureaCode", healbureaCode);
		params.put("currentTime", DateUtil.dateTime(new Timestamp(System.currentTimeMillis())));
		if(StringUtils.isNotBlank(projCode)){
			sql.append(" and c.proj_code = :projCode ");
			params.put("projCode", projCode);
		}
		if(StringUtils.isNotBlank(name)){
			sql.append(" and c.name like :name ");
			params.put("name", "%"+name+"%");
		}
		sql.append(" order by c.proj_code desc ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 获取当前卫生局未申报药品列表
	 *@author wangbing
	 *@date 2015年6月12日 下午4:13:54
	 *@param page
	 *@return
	 */
	@Override
	public Pagination getNoDeclareDrugList(Pagination page){
		String projId = page.getConditions().get("projId");
		String healbureaId = page.getConditions().get("healbureaId");
		String healbureaCode = getOrgCodeById(healbureaId);
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String compName = page.getConditions().get("compName");
		//未申报的药品
		StringBuffer sql = new StringBuffer()
		.append("	SELECT a.proj_drug_id projDrugId,b.drug_code drugCode,c.qualityspec_name drugName,")
		.append("	c.form_name formName,c.spec_name specName,b.factor factor,b.unit_name unit,      ")
		.append("	b.material_name materialName,b.comp_name prodPompName,b.prod_id drugId,a.proj_id projId,b.comp_code compCode")        
		.append("	FROM  bid_projdrugs a      ")
		.append("	LEFT JOIN  std_proddruginfo b    ON a.drug_code=b.drug_code")    
		.append("	LEFT JOIN std_drugqualityspec c ON c.qualityspec_id = b.qualityspec_id")       
		.append("	WHERE  a.proj_id = :projId  AND NOT EXISTS")
		.append("	(SELECT * FROM  bid_hospplan d WHERE d.proj_id=a.proj_id")  
		.append("	AND d.drug_code=a.drug_code   AND d.hea_bur_code = :healbureaCode) ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("projId", projId);
		params.put("healbureaCode", healbureaCode);
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (c.qualityspec_name like :drugName or c.qualityspec_name_spel like :drugName) ");
			params.put("drugName", "%"+drugName+"%");
		}
		if(StringUtils.isNotBlank(compName)){
			sql.append(" and (b.comp_name like :compName or b.comp_name_spel like :compName) ");
			params.put("compName", "%"+compName+"%");
		}
		sql.append(" order by b.drug_code ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 获取当前卫生局已申报药品列表
	 *@author wangbing
	 *@date 2015年6月28日 
	 *@param page
	 *@return
	 */
	@Override
	public Pagination getDeclareDrugList(Pagination page){
		String projId = page.getConditions().get("projId");
		String healbureaId = page.getConditions().get("healbureaId");
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String prodPompId = page.getConditions().get("prodPompId");
		String healbureaCode = getOrgCodeById(healbureaId);
		//已申报的药品		
		StringBuffer sql = new StringBuffer()
		.append("	SELECT d.proj_plan_id projPlanId,d.proj_id projId,b.prod_id drugId,")
		.append("	b.drug_code drugCode,c.qualityspec_name drugName,c.form_name formName,c.spec_name specName,    ")    
		.append("	b.factor factor,b.unit_name unit,b.material_name materialName,b.comp_name prodPompName,b.comp_code compCode,       ")
		.append("	d.lasty_pro_amt lastyProAmt,d.plan_pro_amt planProAmt,d.pro_method proMethod,       ")
		.append("	d.quality_requires qualityRequires   FROM     bid_hospplan d ")
		.append("	LEFT JOIN     std_proddruginfo b    ON d.drug_code=b.drug_code  ")
		.append("	LEFT JOIN std_drugqualityspec c ON c.qualityspec_id = b.qualityspec_id")       
		.append("	WHERE  d.proj_id = :projId AND d.hea_bur_code = :healbureaCode ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("projId", projId);
		params.put("healbureaCode", healbureaCode);
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (c.qualityspec_name like :drugName or c.qualityspec_name_spel like :drugName) ");
			params.put("drugName", "%"+drugName+"%");
		}
		if(StringUtils.isNotBlank(prodPompId)){
			sql.append(" and (b.comp_name like :compName or b.comp_name_spel like :compName) ");
			params.put("compName", "%"+prodPompId+"%");
		}
		sql.append(" order by b.drug_code ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 删除申报药品
	 *@author wangbing
	 *@date 2015年6月28日 
	 *@param page
	 *@return
	 */
	@Override
	public int deleteData(List<String> idList){
		StringBuffer sql = new StringBuffer()
		.append(" delete from bid_hospplan where proj_plan_id in (:idList)");
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameterList("idList", idList);
		int count = query.executeUpdate();
		getSession().flush();
		return count;
	}
	
	@Override
	public int addDrugData(Map<String,String> data){
		String projPlanId  = UUID.randomUUID().toString();
		String projId = data.get("projId");
		String drugCode = data.get("drugCode");
		String heaBurCode = getOrgCodeById(data.get("healbureaId"));
		String areaId = getAreaIdById(data.get("healbureaId"));
		String lastyProAmt = data.get("lastyProAmt");
		String planProAmt = data.get("planProAmt");
		String proMethod = data.get("proMethod");
		String qualityRequires = data.get("qualityRequires");
		String updDatetime = data.get("updDatetime"); 
		String userId = data.get("userId");
		
		Session session = getSession();
		StringBuffer sql = new StringBuffer()
		.append("insert into bid_hospplan values (?,?,?,?,?,?,?,?,?,?,?,?)");
		Query query = session.createSQLQuery(sql.toString());
		query.setParameter(0, projPlanId);
		query.setParameter(1, projId);
		query.setParameter(2, drugCode);
		query.setParameter(3, heaBurCode);
		query.setParameter(4, null);
		query.setParameter(5, areaId);
		query.setParameter(6, lastyProAmt);
		query.setParameter(7, planProAmt);
		query.setParameter(8, proMethod);
		query.setParameter(9, qualityRequires);
		query.setParameter(10, updDatetime);
		query.setParameter(11, userId);
		int count = query.executeUpdate();
		getSession().flush();
		return count;
	}
	@Override
	public int batchAddDrugData(List<Map<String,String>> list){
		int sum = 0;
		if (null != list && list.size() > 0){
			for (int i = 0; i < list.size(); i++){
				int count = addDrugData(list.get(i));
				sum +=count;
			}
			
		}
		return sum;
	}
	
	/**
	 * 
	 *@category 根据机构id获取机构code
	 *@author wangbing
	 *@date 2015年6月12日 下午3:33:22
	 *@param page
	 *@return
	 */
	private String getOrgCodeById(String orgId){
		Session session = getSession();
		String sql = "select hea_bur_code from std_healthbureau where hea_bur_id = :orgId ";
		Query query = session.createSQLQuery(sql);
		query.setString("orgId", orgId);
		return query.uniqueResult().toString();
	}
	
	/**
	 * 
	 *@category 根据机构id获取机构地区
	 *@author wangbing
	 *@date 2015年6月12日 下午3:33:22
	 *@param page
	 *@return
	 */
	private String getAreaIdById(String orgId){
		Session session = getSession();
		String sql = "select area_id from std_healthbureau where hea_bur_id = :orgId ";
		Query query = session.createSQLQuery(sql);
		query.setString("orgId", orgId);
		return query.uniqueResult().toString();
	}
}